# -*- coding: utf-8 -*-
# @Time    : 18-6-28 上午9:09
# @Author  : hp
# @Email   : shaoeric@foxmail.com
import os

from flask import Blueprint, render_template, request, url_for, redirect, session, views, g, send_file, send_from_directory, make_response
from utils import restful
from datetime import date, timedelta, time
from config import db, cursor, CMS_USER_ID
from .decorator import login_required

bp = Blueprint('cms', __name__, url_prefix='/cms')


today = date.today()
lastWeek = today - timedelta(days=7)
lastMonth = today - timedelta(days=30)
yesterday = today - timedelta(days=1)

@bp.route('/')
@login_required
def index():
    # 过去7天销量
    saleNumberSql = "select sum(sanum) from Sale where saletime between '{}' and '{}'".format(lastWeek, today)
    cursor.execute(saleNumberSql)
    saleNumber = cursor.fetchone()[0]

    # 过去7天销售额
    saleAmountSql = "select sum(salesamount * sanum)from Sale where saletime between '{}' and '{}'".format(lastWeek, today)
    cursor.execute(saleAmountSql)
    saleAmount = cursor.fetchone()[0]
    # 过去7天进货支出
    buyInSql = "select sum(buynum* buyoffer) from Buyin where buytime between '{}' and '{}'".format(lastWeek, today)
    cursor.execute(buyInSql)
    buyAmount = cursor.fetchone()[0]

    # 热门
    hotBookSql = "SELECT isbn from Sale where saletime BETWEEN '{}' and '{}'GROUP BY isbn having sum(sanum)>3".format(lastWeek, today)
    cursor.execute(hotBookSql)
    hotBookNum = len(cursor.fetchall())

    # 周总收入  销售-进货-退货(查询过去7天的退货额)
    returnBackAmountSql = "select sum(remoney * renum) from Retur where retime BETWEEN '{}' and '{}'".format(lastWeek, today)
    cursor.execute(returnBackAmountSql)
    returnBackAmount = cursor.fetchone()[0]
    weeklyIncome = saleAmount - buyAmount - returnBackAmount

    # 30天销量
    monthlySaleNumberSql = "select sum(sanum) from Sale where saletime between '{}' and '{}'".format(lastMonth, today)
    cursor.execute(monthlySaleNumberSql)
    monthlySaleNumber = cursor.fetchone()[0]

    # 30天利润
    monthlySaleSql = "select sum(salesamount * sanum)from Sale where saletime between '{}' and '{}'".format(lastMonth, today)
    monthlyBuySql = "select sum(buynum* buyoffer) from Buyin where buytime between '{}' and '{}'".format(lastMonth, today)
    monthlyReturnBackAmountSql = "select sum(remoney * renum) from Retur where retime BETWEEN '{}' and '{}'".format(lastMonth, today)
    cursor.execute(monthlySaleSql)
    monthlySale = cursor.fetchone()[0]
    cursor.execute(monthlyBuySql)
    monthlyBuy = cursor.fetchone()[0]
    cursor.execute(monthlyReturnBackAmountSql)
    monthlyReturnBackAmount = cursor.fetchone()[0]
    monthlyIncome = monthlySale - monthlyBuy - monthlyReturnBackAmount
    # 7天每天销量/销售额列表
    weeklySaleListSql = "select SUM(Sale.sanum), SUM(salesamount * sanum) from Sale where Sale.saletime BETWEEN '2018-6-24' and '2018-7-1' GROUP BY saletime"
    cursor.execute(weeklySaleListSql)
    weeklySaleList = cursor.fetchall()

    weekSaleNumberList = list(map(lambda x: int(x[0]), weeklySaleList))
    weekSaleAmountList = list(map(lambda x: int(x[1]), weeklySaleList))

    # 最近一天的销售情况
    yesterdaySaleSql = "select Sale.ordernum, Sale.isbn, Infor.bname, Sale.saletime, Sale.salesamount*Sale.sanum from Sale, Infor where Sale.saletime BETWEEN '{}' and '{}' and Sale.isbn = Infor.isbn ORDER BY saletime desc".format(yesterday, today)
    cursor.execute(yesterdaySaleSql)
    yesterdaySale = cursor.fetchall()

    # 最近一天的进货情况
    yesterdayBuyInSql = "select Buyin.isbn, Infor.bname, Buyin.buynum, Buyin.buyoffer, Buyin.buytime from Buyin, Infor where Buyin.isbn=Infor.isbn and buytime BETWEEN '{}' and '{}' ORDER BY buytime desc".format(yesterday, today)
    cursor.execute(yesterdayBuyInSql)
    yesterdayBuyIn = cursor.fetchall()

    # 库存
    InventorySql = "select Infor.isbn, Infor.bname, Inventory.rest, Infor.writer, Infor.publish, Infor.inprice from Infor, Inventory where Infor.isbn=Inventory.isbn order by Inventory.rest"
    cursor.execute(InventorySql)
    Inventory = cursor.fetchmany(20)

    dict = {
        'lastweek': lastWeek,
        'today': today,
        'saleNumber': saleNumber,   # 过去7天销量
        'saleAmount': saleAmount,   # 过去7天销售额
        'buyAmount': buyAmount,   # 过去7天进货支出
        'hotBookNum': hotBookNum,   # 过去7天热门书的种类数
        'weeklyIncome': weeklyIncome,   # 7天总收入
        'monthlySaleNumber': monthlySaleNumber,  # 30天销量
        'monthlyIncome': monthlyIncome,  # 30天销量
        'weekSaleNumberList': weekSaleNumberList, # 7天销售量列表
        'weekSaleAmountList': weekSaleAmountList,  # 7天销售额列表
        'yesterdaySale': yesterdaySale,    # 过去一天销售情况
        'yesterdayBuyIn': yesterdayBuyIn,   # 过去一天进货情况
        'Inventory': Inventory   # 库存
    }
    return render_template('cms/index.html', **dict)

# 登录
class LoginView(views.MethodView):
    def get(self, message=None):
        return render_template('cms/login.html', message=message)
    def post(self):
        from hashlib import md5

        def genearteMD5(string):
            # 创建md5对象
            hl = md5()
            # 此处必须声明encode  否则报错为：hl.update(str)    Unicode-objects must be encoded before hashing
            hl.update(string.encode(encoding='utf-8'))
            return hl.hexdigest()[8: -8]

        username = request.values.get('username')
        _password = request.values.get('password')

        # 根据username得到数据库中的信息，
        sql = "select * from User where username='{}'".format(username)
        cursor.execute(sql)
        try:
            username_result, pwd_result, salt_result, name = cursor.fetchone()
            # 将salt和password拼接 md5加密
            password = genearteMD5(str(salt_result) + _password)
            if password == pwd_result:
                session[CMS_USER_ID] = username_result
                session['name'] = name
                return redirect(url_for('cms.index'))
            else:
                return self.get(message='密码错误')
        except TypeError:
            print("no")
            return self.get(message='用户名不存在')

bp.add_url_rule('/login/', view_func=LoginView.as_view('login'))

# 注销
@bp.route('/logout/')
@login_required
def logout():
    session.clear()
    return redirect(url_for('cms.login'))


# 改变密码
class ResetPassword(views.MethodView):
    decorators = [login_required]
    def get(self, message=None):
        return render_template('cms/changepwd.html', message=message)
    def post(self):
        pwd = request.values.get('password')
        repwd = request.values.get('repassword')
        # 如果两次密码不一致
        if pwd != repwd:
            return self.get(message='两次密码不一致')
        # 密码一致
        # 判断用户名和密码是否正确
        from hashlib import md5
        def genearteMD5(string):
            # 创建md5对象
            hl = md5()
            # 此处必须声明encode  否则报错为：hl.update(str)    Unicode-objects must be encoded before hashing
            hl.update(string.encode(encoding='utf-8'))
            return hl.hexdigest()[8: -8]

        saltQuery = "select salt from User where username='{}'".format(g.username)
        cursor.execute(saltQuery)
        salt = cursor.fetchone()[0]
        newpwd = genearteMD5(str(salt)+ pwd)
        sql = "update User set password='{}' where username='{}'".format(newpwd, g.username)
        try:
            cursor.execute(sql)
            db.commit()
            return redirect(url_for('cms.index'))
        except:
            return self.get(message='修改失败')

bp.add_url_rule('/reset/', view_func=ResetPassword.as_view('reset'))


# 热门
class HotBook(views.MethodView):
    decorators = [login_required]
    def get(self, num=2, start=lastWeek, end=today):
        hotBookSql = "SELECT Infor.isbn, Infor.bname, sum(Sale.sanum), Inventory.rest, Infor.writer, Infor.publish, Infor.inprice, Infor.price from Infor, Sale, Inventory where Infor.isbn in(SELECT isbn from Sale where saletime BETWEEN '{}' and '{}' GROUP BY isbn having sum(sanum)>'{}') and Infor.isbn=Sale.isbn and Infor.isbn=Inventory.isbn Group by Infor.isbn ORDER BY sum(Sale.sanum) DESC".format(start, end, num)
        cursor.execute(hotBookSql)
        hotBook = cursor.fetchall()

        return render_template('cms/hotBook.html', hotBook=hotBook)
    def post(self):
        num = request.values.get('num')
        start = request.values.get('start')
        end = request.values.get('end')
        return self.get(num=num, start=start, end=end)

bp.add_url_rule('/hotbook/', view_func=HotBook.as_view('hotbook'))


# 搜索  todo
@bp.route('/search/', methods=['POST'])
def search():
    if request.method == 'GET':
        return '404 NOT FOUND'
    if request.method == 'POST':
        from time import strptime
        def isVaildDate(self, date):  # 判断字符串是否是日期格式
            try:
                strptime(date, "%Y-%m-%d")
                return True
            except:
                return False
        keyword = request.values.get('word')
        pass


class RecentOrder(views.MethodView):
    decorators = [login_required]
    def get(self, start=lastWeek, end=today):
        pass


# 统计报表
class MonthlyReport(views.MethodView):
    decorators = [login_required]
    def get(self):
        order = request.values.get('order')
        start = request.values.get('start')
        end = request.values.get('end')

        order = 'sanum' if not order else order
        start = lastMonth if not start else start
        end = today if not end else end

        MonthlyReportSql = "SELECT Infor.isbn, Infor.bname, sum(Sale.sanum), Inventory.rest, Infor.writer, Infor.publish, Infor.inprice, Infor.price from Infor, Sale, Inventory where Infor.isbn=Sale.isbn and Infor.isbn=Inventory.isbn and Sale.saletime BETWEEN '{}' and '{}' GROUP BY Infor.isbn ORDER BY SUM(Sale.{}) DESC".format(start, end, order)
        cursor.execute(MonthlyReportSql)
        monthlyReport = cursor.fetchall()

        saleNumberSql = "select sum(sanum) from Sale where saletime between '{}' and '{}'".format(start, end)
        cursor.execute(saleNumberSql)
        saleNumber = cursor.fetchone()[0]

        saleAmountSql = "select sum(salesamount * sanum)from Sale where saletime between '{}' and '{}'".format(start, end)
        cursor.execute(saleAmountSql)
        saleAmount = cursor.fetchone()[0]

        dict = {
            'monthlyReport': monthlyReport,  # 排名报表
            'saleNumber': saleNumber,  # 销售总量
            'saleAmount': saleAmount,  # 销售总额
            'order': order,
            'start': start,
            'end': end
        }
        return render_template('cms/charts.html', **dict)

bp.add_url_rule('/report/', view_func=MonthlyReport.as_view('report'))


@bp.route('/generateReport/')
@login_required
def generateReport():
    # 生成报告文件
    order = request.values.get('order')
    start = request.values.get('start')
    end = request.values.get('end')

    order = 'sanum' if not order else order
    start = lastMonth if not start else start
    end = today if not end else end

    MonthlyReportSql = "SELECT Infor.isbn, Infor.bname, sum(Sale.sanum), Inventory.rest, Infor.writer, Infor.publish, Infor.inprice, Infor.price from Infor, Sale, Inventory where Infor.isbn=Sale.isbn and Infor.isbn=Inventory.isbn and Sale.saletime BETWEEN '{}' and '{}' GROUP BY Infor.isbn ORDER BY SUM(Sale.{}) DESC".format(
        start, end, order)
    cursor.execute(MonthlyReportSql)
    monthlyReport = cursor.fetchall()
    monthlyReport = list(map(lambda item: list(item), monthlyReport))

    # 写入文件
    from tools.openpyxltest import writeExcel
    import os
    filename = writeExcel(['书籍号', '书名', '销量', '库存量', '作者', '出版社', '进价', '售价'], monthlyReport, "{}_{}".format(start, end))
    # 回复请求：下载文件类型
    directory = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'data')

    response = make_response(send_from_directory(directory, filename, as_attachment=True))
    response.headers["Content-Disposition"] = "attachment; filename={}".format(filename.encode().decode('utf-8'))
    return response


@bp.route('/return/', methods=['GET', 'POST'])
@login_required
def tuihuo():
    if request.method == 'GET':
        return render_template('cms/return.html')
    else:
        order = request.values.get('ordernum')
        cursor.execute(
            "SELECT  Sale.ordernum, Sale.isbn, Infor.bname, Sale.sanum, Sale.salesamount, Sale.buyer, Sale.saletime from Sale, Infor where Sale.isbn=Infor.isbn and Sale.ordernum='{}'".format(order))
        order_info = cursor.fetchall()
        return render_template('cms/return.html', order_info=order_info)


class GenerateReturnReport(views.MethodView):
    decorators = [login_required]
    # 生成退货报告
    # def get(self, data=None):
    #     print("渲染")
    #     # return render_template('cms/generateReturnReport.html', **data)
    #     return redirect(url_for('cms.returnReport', data=data))

    # def get(self, excelName):
        # 回复请求：下载文件类型
        # directory = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'data')
        #
        # response = make_response(send_from_directory(directory, excelName, as_attachment=True))
        # response.headers["Content-Disposition"] = "attachment; filename={}".format(excelName.encode().decode('utf-8'))
        # print(response.__repr__())
        # return response

    def post(self):
        # data = request.get_json()
        # orderNum = data['orderNum'][0]
        # ISBN = data['ISBN'][0:-1]
        # returnNum = data['returnNum'][0:-1]
        # sign = data['sign']
        # {'orderNum': ['16485417', None], 'ISBN': ['9787547215593', None], 'returnNum': ['3', None], 'sign': '阿萨德发 '}
        ordernum = request.values.get('ordernum')
        isbn = request.values.getlist('isbn')
        sign = request.values.get('sign')

        # 得到退货订单  图书编号  数量 买家签名
        print(ordernum, isbn, sign)

        sql = "select Sale.isbn, Infor.bname, Sale.salesamount, Sale.sanum from Sale, Infor where Sale.isbn = Infor.isbn and Sale.ordernum = '{}'".format(ordernum)
        cursor.execute(sql)
        result = cursor.fetchall()
        print(result)
        returnAmount = 0
        returnNum = []
        for item in result:
            if item[0] in isbn:  # 如果书被退了
                returnAmount += item[2] * item[3]
                returnNum.append(item[3])
            else:
                returnNum.append(0)
        #
        # tableValues = []
        # tableTitle = ['订单号', '图书号', '书名', '退货数']
        # for i in range(len(result)):
        #     tableValues.append([orderNum, ISBN[i], result[i][1], returnNum[i]])

        dict = {
            'result':result,
            'returnNum': returnNum,
            'sign': sign,
            'orderNum': ordernum,
            'returnAmount': returnAmount
        }
        return render_template('cms/generateReturnReport.html', **dict)
        # 写入excel
        #
        # from openpyxl import Workbook
        # filename = "return_" + str(orderNum)
        # excelPath = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'data')
        # excelName = filename + '.xlsx'
        # ExcelFullName = os.path.join(excelPath, excelName)
        # wb = Workbook()
        # ws = wb.active
        # for col in range(len(tableTitle)):
        #     c = col + 1
        #     ws.cell(row=1, column=c).value = tableTitle[col]
        #
        # # 数据表基本信息
        # for row in range(len(tableValues)):
        #     ws.append(tableValues[row])
        #
        # ws.cell(row=2+len(tableValues), column=1).value = "总退款："
        # ws.cell(row=2+len(tableValues), column=2).value = str(returnAmount) + "元"
        # ws.cell(row=2+len(tableValues), column=4).value = "退款日期："
        # ws.cell(row=2+len(tableValues), column=5).value = str(today)
        # ws.cell(row=3+len(tableValues), column=1).value = "退款人:"
        # ws.cell(row=3+len(tableValues), column=2).value = sign
        #
        # wb.save(filename=ExcelFullName)
        #
        # directory = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'data')
        #
        # response = make_response(send_from_directory(directory, excelName, as_attachment=True))
        # response.headers["Content-Disposition"] = "attachment; filename={}".format(excelName.encode().decode('utf-8'))
        # return response

bp.add_url_rule('/generateReturnReport/', view_func=GenerateReturnReport.as_view('generateReturnReport'))


@bp.route('/printReturnReport/')
def printReturnReport():
    ordernum = request.values.get('ordernum')
    import os
    from PIL import ImageGrab
    im = ImageGrab.grab()
    path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'data')
    filename = str(today) + "_" + ordernum + '.jpg'
    filepath = os.path.join(path, filename)
    im.save(filepath)

    response = make_response(send_from_directory(path, filename, as_attachment=True))
    response.headers["Content-Disposition"] = "attachment; filename={}".format(filename.encode().decode('utf-8'))
    return response
